<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of PACTS_config
 *
 * @author colin
 */

class PactsCfg extends PACTS_config_variables{
    //put your code here

    private $buffered_content = "";
    public $use_buffered_content = false;
    // Customised settings

    function init($bDebugOn = false, $bUseBufferedContent = false, $bIgnoreSQLerrors = false) {
        //print "In BaseClass constructor\n";
        $this->use_buffered_content = $bUseBufferedContent;
        $this->date = date ("h:ia d F Y");

        $this->aMySQLType = array(
                   'VARCHAR' => $this->textbox,
                   'TEXT'=> $this->textareabox,
                   'TINYTEXT'=>$this->textbox,
                   'MEDIUMTEXT'=>$this->textbox,
                   'LONGTEXT'=>$this->textareabox,
                   'CHAR'=>$this->textbox,
                   'DATE'=>$this->datebox,
                   'DATETIME'=>$this->timedatebox,
                   'TIMESTAMP'=>$this->timebox,
                   'TIME'=>$this->timebox,
                   'YEAR'=>$this->yearbox,
                   'TINYINT'=>$this->textbox,
                   'SMALLINT'=>$this->textbox,
                   'MEDIUMINT'=>$this->textbox,
                   'INT'=>$this->textbox,
                   'BIGINT'=>$this->textbox,
                   'FLOAT'=>$this->textbox,
                   'DOUBLE'=>$this->textbox,
                   'DECIMAL'=>$this->textbox,
                   'BLOB'=>$this->imagebox,
                   'TINYBLOB'=>$this->imagebox,
                   'MEDIUMBLOB'=>$this->imagebox,
                   'LONGBLOB'=>$this->imagebox,
                   'ENUM'=>$this->enumcheckbox,
                   'SET'=>'::');

        $this->aFieldLookUpTableList = array(
                   'RoleTypeCode' => $this->RoleTypeTable,
                   'GenderCode' => $this->GenderCodeTable,
                   'Title' => $this->TitleTable,
                   'ChurchIntroductionMethod' => $this->ChurchIntroductionMethodTable,
                   'ChurchDepartureReason' => $this->ChurchDepartureReasonTable,
                   'MaritalStatusCode' => $this->MaritalStatusCodeTable,
                   'EducationalInstitutionName' => $this->EducationInstitutionTable,
                   'AgeRange' => $this->AgeRangeTable,
                   'PremiseType' => $this->PremiseTypeTable,
                   'ResidentialPremiseType' => $this->PremiseTypeTable,
                   'StreetTypeCode' => $this->StreetTypeCodeTable,
                   'ResidentialStreetTypeCode' => $this->StreetTypeCodeTable,
                   'ResidentialCountry' => $this->CountryTable,
                   'PostalAddressCountry' => $this->CountryTable,
                   'FamilyId' => $this->Family,
                   'WorkTypeCode' => $this->WorkTypeCodeTable



        );

        // connect to database
        $this->ConnectToDatabase($bIgnoreSQLerrors);
        $this->DebugOn = $bDebugOn;
    }//__construct()











    function InsertAuditTrailEntry($OldData, $NewData, $ChangeType, $TableName, $FieldName, $UserId, $PageName,$RecordId)
    {
        //InsertAuditTrailEntry("my change details","Edit","Family","colly");
        //exit;

        $sql = 'INSERT INTO `AuditTrail` ( `AuditTrailId` , `AuditUserId` , `AuditTMS` , `ChangeTableName` , `FieldName` , `ChangeType` , `OldData`, `NewData`, `PageName` , `RecordId`) ';
        $sql .= 'VALUES ( \'\', \''.$UserId.'\', NOW() , \''.$TableName.'\',  \''.$FieldName.'\', \''.$ChangeType.'\', \''.mysql_escape_string($OldData).'\' , \''.mysql_escape_string($NewData).'\',  \''.$PageName.'\',  \''.$RecordId.'\' );';
        $sql .= '';

        // $sql = mysql_escape_string($sql);
        //echo "\n<pre>";
        //echo $sql;
        //echo "\n</Pre>";
        // $result = mysql_query("SELECT * from AxuditTrail WHERE 1=1",$this->dblink)

        $result = mysql_query($sql,$this->dblink)
        or die("Invalid AUDIT TRAIL query: " . mysql_error());
    }



    Function getAddressError($err1= "", $err2 = "", $err3 = "", $strDelim = ", " ){

        $errMsg = "";
        $bEntry = False;

        $errMsg = $err1;

        if (strlen($errMsg)>0 and strlen($err2)>0) {
            $errMsg .= $strDelim ;
        }
        $errMsg .= $err2;

        if (strlen($errMsg)>0 and strlen($err3)>0) {
            $errMsg .= $strDelim ;
        }
        $errMsg .= $err3;

        return $errMsg ;

    }




    Function printPDFTitlePage($pdf, $strTitle, $strSubTitle, $intPageSizeX , $intPageSizeY, $intTopOfPageX, $intTopOfPageY, $intMinBottomPageY, $font1_normal, $font1_normal_bold, $intPageCount=0, $intPageSizetoUseX = 0, $intPageSizetoUseY = 0 ){

        global $SoftwareShortName;
        global $SoftwareLongName;
        global $SoftwareVersion;
        global $SoftwareDate;
        global $SoftwareUserName;
        global $SoftwareAuthorName;
        //global $SoftwareLogo;
        global $SoftwareLogoPDF;

        global $SoftwareWebsite;
        global $_SESSION;


        $intPageBorderIndent =20;
        // font sizes for fullsize title

        $NormalFont = "Helvetica";
        $BoldFont = "Helvetica-Bold";
        $smallFontSize = 10; // orig 14     // Role
        $medsmallFontSize = 12; // orig 16  // address
        $mediumFontSize = 18; // orig 20 // first Name
        $largeFontSize = 20; // orig 24  // Surname and Home PhoneNumber
        $hugeFontSize = 60; // orig 40


        $bSmallFonts = FALSE;


        $linespacing = 15;
        $detailsY=($intPageSizeY/6);

        if($intPageSizeX > $intPageSizeY) {
            // landscape
            $left_detailsX=70;
            $right_detailsX=521;   // $intPageSizeX/2 +100
            $intMinBottomPageY = $intMinBottomPageY-12;
        } else {
            // portrait
            $left_detailsX=70;
            $right_detailsX=$intPageSizeX/2+50;   // $intPageSizeX/2 +100
            $intMinBottomPageY = $intMinBottomPageY+5;
        }



        if($intPageSizetoUseX==0) {
            $intPageSizetoUseX = $intPageSizeX;
            $intPageSizeXX = $intPageSizeX;
            $intPageSizeXX = 0;
            $intCenterX = $intPageSizetoUseX/2;
            $bb = "FALSE";
            $intlogoindent = 260;
        } else {
            $bSmallFonts = TRUE;
            $intPageSizeXX = $intPageSizetoUseX;
            $intCenterX = $intPageSizetoUseX + $intPageSizetoUseX/2;
            $bb = "TRUE";
            $left_detailsX=$intCenterX-180;
            $right_detailsX=$intCenterX+50;   // $intPageSizeX/2 +100
            $intMinBottomPageY = $intMinBottomPageY+5;
            $intlogoindent = 175;
        }
        if($intPageSizetoUseY==0) {
            $intPageSizetoUseY = $intPageSizeY;
            $intPageSizeYY = $intPageSizeY;
            $bc = "FALSE";
        } else {
            $bSmallFonts = TRUE;
            $intPageSizeYY = $intPageSizetoUseY;
            $bc = "TRUE";
        }

        if($bSmallFonts) {
            $smallFontSize = 8; // orig 14     // Role
            $medsmallFontSize = 10; // orig 16  // address
            $mediumFontSize = 14; // orig 20 // first Name
            $largeFontSize = 16; // orig 24  // Surname and Home PhoneNumber
            $hugeFontSize = 32; // orig 40
        }

        //echo "$pdf, $strTitle, $strSubTitle, $intPageSizeX , $intPageSizeY, $intTopOfPageX, $intTopOfPageY, $intMinBottomPageY, $font1_normal, $font1_normal_bold, $intPageCount=0<p>";
        //exit;
        //echo "intPageSizeXX = $intPageSizeXX<p>";
        //echo "intPageSizeYY = $intPageSizeYY<p>";
        //exit;



        pdf_begin_page($pdf, $intPageSizeX , $intPageSizeY );

        $intPageCount++;

        $y = $intTopOfPageY;
        $x = $intTopOfPageX;
        pdf_setfont($pdf, $font1_normal, 8);
        //    pdf_show_xy($pdf, "intPageSizeX..$intPageSizeX", $x-40, $y);
        //    pdf_show_xy($pdf, "intPageSizeY..$intPageSizeY", $x-40, $y-20);
        //    pdf_show_xy($pdf, "$x..$y", $x-80, $y);
        //    pdf_show_xy($pdf, "$x..$y", $x-100, $y);
        //    pdf_setfont($pdf, $font1_normal, 60);
        pdf_setfont($pdf, $font1_normal_bold, $hugeFontSize);
        $length = pdf_stringwidth($pdf,$strTitle,$font1_normal_bold, $hugeFontSize);
        //    pdf_show_xy($pdf, "TITLE", ($intPageSizeX/2)-($length/2), ($intPageSizeY/2)+100);
        ///////////////////////////
        //    pdf_show_xy($pdf, $strTitle, ($intPageSizeXX/2)-($length/2), ($intPageSizeYY/2)+100);
        pdf_show_xy($pdf, $strTitle, $intCenterX-($length/2), ($intPageSizeYY/2)+100);
        ///////////////////////////


        pdf_setfont($pdf, $font1_normal, $largeFontSize);
        $fontsize = pdf_get_value($pdf,'fontsize');
        $length = pdf_stringwidth($pdf, $strSubTitle,$font1_normal, $largeFontSize);
        $textHeight = 1;
        $height_change = 0;


        pdf_setfont($pdf, $font1_normal, $mediumFontSize);
        pdf_show_boxed($pdf,$strSubTitle, $intPageSizeXX+$intPageBorderIndent+10, ($intPageSizeY/2)-50-200/2, $intPageSizeX-$intPageSizeXX-($intPageBorderIndent+10)*2, 200,"center");

        pdf_setfont($pdf, $font1_normal_bold, $smallFontSize);
        pdf_show_xy($pdf, $SoftwareShortName, $right_detailsX, $detailsY-$linespacing*1);
        pdf_setfont($pdf, $font1_normal, $smallFontSize);
        pdf_show_xy($pdf, $SoftwareLongName, $right_detailsX, $detailsY-$linespacing*2);
        pdf_show_xy($pdf, "V".$SoftwareVersion." ".chr(169)." ".$SoftwareAuthorName." ".$SoftwareDate, $right_detailsX, $detailsY-$linespacing*3);
        pdf_show_xy($pdf, $SoftwareUserName, $right_detailsX, $detailsY-$linespacing*4);

        pdf_setfont($pdf, $font1_normal_bold, $medsmallFontSize );
        pdf_show_xy($pdf, "Date : ".date("g:i a j F Y"), $left_detailsX, $detailsY-$linespacing*3);// March 10, 2001, 5:16 pm
        pdf_show_xy($pdf, "User : ".$_SESSION['username'], $left_detailsX, $detailsY-$linespacing*4);

        pdf_add_outline($pdf, "Title Page : Page $intPageCount");

        pdf_setfont($pdf, $font1_normal, $smallFontSize);
        // reset X,Y values to defaults
        // insert logo
        $pim = pdf_open_image_file($pdf, "gif", "../".$SoftwareLogoPDF);
        $pdf_im_width = pdf_get_value($pdf, "imagewidth", $pim);
        $pdf_im_height = pdf_get_value($pdf, "imageheight", $pim);
        $pdf_im_ratio = (double)100/$pdf_im_width;

        pdf_place_image($pdf, $pim, $intCenterX- $intlogoindent, $intPageSizeY-100, $pdf_im_ratio*2.75 );
        pdf_close_image($pdf, $pim);

        // draw rectangle
/*
                                pdf_show_xy($pdf, "intPageSizeXX : ".$intPageSizeXX, 200, 200);
                                pdf_show_xy($pdf, "intPageSizeYY : ".$intPageSizeYY, 200, 250);
                                pdf_show_xy($pdf, "intPageSizeX  : ".$intPageSizeX, 200, 350);
                                pdf_show_xy($pdf, "intPageSizeY  : ".$intPageSizeY, 200, 300);
                                pdf_setfont($pdf, $font1_normal, 20);
                        pdf_show_xy($pdf, "bb = $bb, bc=$bc ", 200, 480);
*/

        pdf_rect($pdf, $intPageSizeXX+$intPageBorderIndent , $intPageBorderIndent , $intPageSizeX-$intPageSizeXX-$intPageBorderIndent*2, $intPageSizeY-$intPageBorderIndent*2);

        pdf_stroke($pdf);
        pdf_end_page($pdf);
        //pdf_end_page($pdf);
        return;
    }


    function getListHeader(
        $listfilter_htmlcode="",
        $selectedValue="",
        $linkpage_az,
        $linkpage_all,
        $Title,
        $listfilter,
        $filterArgs="")
    {

        $selectedValue = strtoupper($selectedValue);

        global $content2_mouseover;
        $content2_mouseover = "";
        $selectedText = "<b><u>";

        $selectedText = 'class="sel"';
        $link = $this->remove_duplicated_amps($linkpage_az."&amp;".$listfilter_htmlcode."alphabet");

        //$link

        echo '<div id="page_header">'."\n";
        echo '<ul><li class="titlename2">'.$Title.'</li>'."\n";
        echo '<li class="type2">Surname starting with the letter...</li>'."\n";
        echo '</ul>'."\n";
        echo '</div>'."\n";

        echo '<div id="page_filter">'."\n";
        $WhereFilter =  $this->FilterList($listfilter,$this->remove_duplicated_amps($linkpage_az."&amp;"),$listfilter_htmlcode,$this->remove_duplicated_amps($filterArgs."&amp;alphabet=".$selectedValue));

        $AZarray = array("all","a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z");

        echo '<ul>'."\n";
/*
        if ($selectedValue == ""){
            $selectedValue="all";
        }
*/
        foreach ($AZarray as $i => $value) {
            $cur = $AZarray[$i];

            if(strtoupper($selectedValue)==strtoupper($cur))
            {
                echo '<li '.$selectedText.'>';
            } else {
                echo '<li>';
            }
            echo '<a href="'.$link.'='.$cur.'">'.$cur.'</a></li>'."\n";
            unset($AZarray[$i]);
        }
        echo '</ul>'."\n";
        echo '</div> <!-- end div page_filter -->'."\n";

        Return $WhereFilter;
    } // end function

    function getSchoolYear($KindergartenCommenceYear) {
        $SchoolYear = "";
        if(strlen($KindergartenCommenceYear)>0) {
            $currentyear = Date("Y");
            $yeardiff = $currentyear - $KindergartenCommenceYear;

            $result_2 = mysql_query("SELECT * FROM $this->educationgradetablename",$this->dblink);
            while ($row_2 = mysql_fetch_array($result_2)) {
                $operator = $row_2['YearCountOperator'];
                if($operator=="=") {
                    $operator = "==";
                }
                $yearcount = $row_2['YearCount'];
                $form = "return(".$yeardiff.$operator.$yearcount.");";
                //   echo "trying '$form' <br />";
                //  $a = FALSE;
                $a = eval($form);
                //  echo "a = '$a'<br />";
                if($a){
                    //   echo "match on $yeardiff.$operator.$yearcount";
                    $SchoolYear = $row_2['EducationGrade'];
                    break;
                } else {
                    //   echo " no match ! <p><hr>";
                }
            }
        } else {
            $SchoolYear= "";
        }
        return $SchoolYear;
    }

    function FilterList($listfilter,$linkpage_az,$listfilter_htmlcode,$alpha) {

        //        $WhereFilter =  $this->FilterList($listfilter,$linkpage_az."&amp;",$listfilter_htmlcode,$filterArgs."&amp;alphabet=".$selectedValue);


        global $HTTP_SERVER_VARS;
        //        $linkpage_az = $HTTP_SERVER_VARS['PHP_SELF'];//."&amp;";
        //        $linkpage_az = $HTTP_SERVER_VARS['PHP_SELF'];//."&amp;";
        $link = $this->remove_duplicated_amps($linkpage_az.$alpha."&amp;");

        $WhereFilter ="";
        ////"<center><table border=0><tr><td class=\"listfilterTD2\">";
        //echo  "<center><table border=0><tr><td class=\"listfilterTD1\">";
        //echo  "<font class=\"listfilterheader\">FILTER : </font><font class=\"listfilter\">";


        echo '<ul>'."\n";
        //        echo '<li id="listfilterheader">FILTER : </li>'."\n";

        // nothing filtered. ie. ALL
        if(strtoupper($listfilter)=="" || strtoupper($listfilter)=="ALL") {
            echo  '<li class="sel">';
            $WhereFilter = "";
        } else {
            echo  '<li>';
        }
        echo  '<a href="'.$link.'">all</a></li><li>&nbsp;|&nbsp;</li>'."\n";

        if(strtoupper($listfilter)=="CD") {
            echo  '<li class="sel">';
            $WhereFilter .= " AND ChurchDirectoryIndicator = 'Y' ";
        } else {
            echo  '<li>';
        }
        echo  '<a href="'.$link.'listfilter=CD">churchdirectory</a></li><li>&nbsp;|&nbsp;</li>'."\n";

        if(strtoupper($listfilter)=="BA") {
            echo  '<li class="sel">';
            //echo  "<font class=\"listfilterS\">baptism</font> | ";
            $WhereFilter .= " AND BaptismContactIndicator = 'Y' ";
        } else {
            echo  '<li>';
        }
        echo  '<a href="'.$link.'listfilter=BA">baptism</a></li><li>&nbsp;|&nbsp;</li>'."\n";

        if(strtoupper($listfilter)=="CDBA") {
            echo  '<li class="sel">';
            $WhereFilter .= " AND BaptismContactIndicator = 'Y' AND ChurchDirectoryIndicator = 'Y' ";
        } else {
            echo  '<li>';
        }
        echo  '<a href="'.$link.'listfilter=CDBA">churchdirectory and baptism</a></li>'."\n";

        echo '</ul>';

        return $WhereFilter;
    }

    function hideNumbers($strtohide, $hideChar="x"){
        $strtohide = strtr($strtohide, "1", $hideChar);
        $strtohide = strtr($strtohide, "2", $hideChar);
        $strtohide = strtr($strtohide, "3", $hideChar);
        $strtohide = strtr($strtohide, "4", $hideChar);
        $strtohide = strtr($strtohide, "5", $hideChar);
        $strtohide = strtr($strtohide, "6", $hideChar);
        $strtohide = strtr($strtohide, "7", $hideChar);
        $strtohide = strtr($strtohide, "8", $hideChar);
        $strtohide = strtr($strtohide, "9", $hideChar);
        $strtohide = strtr($strtohide, "0", $hideChar);
        return $strtohide;
    }

    function utime (){
        $time = explode( " ", microtime());
        $usec = (double)$time[0];
        $sec = (double)$time[1];
        return $sec + $usec;
    }

    function hideChars($strtohide, $hideChar="x"){

        //  echo "<pre>$strtohide<p>";
        for($i=33; $i<126; $i++) {
            $strtohide = strtr($strtohide, chr($i) , $hideChar);
        }
        //  echo "$strtohide<p>";
        return $strtohide;
    }

    function get_uploaded_file($filename) {
        $tmp_file = get_cfg_var('upload_tmp_dir');
        if (!$tmp_file) {
            $tmp_file = dirname(tempnam('', ''));
        }
        echo "get_cfg_var('upload_tmp_dir')".get_cfg_var('upload_tmp_dir')."<p>";
        $handle = opendir(get_cfg_var('upload_tmp_dir'));
        if ($handle) {
            while (false !== ($file = readdir($handle))) {
                if ($file != "." && $file != "..") {
                    echo "$file<p>\n";
                }
            }
            closedir($handle);
        }
        $tmp_file .= '/' . basename($filename);
    /* User might have trailing slash in php.ini... */
        return (ereg_replace('/+', '/', $tmp_file) == $filename);
    }

    function getLookValue($table, $codeColumn, $codeValue, $ReturnValue)
    {
        $sql =  "SELECT * FROM `$table` WHERE $codeColumn like '".$codeValue."' ;";
        $fResult = mysql_query($sql,$this->dblink) or die("Query Error : ".$sql);
        $FLinks = mysql_fetch_array($fResult);
        if (is_null( $FLinks["$ReturnValue"]) )
        {
            return "";

        } else
        {
            return $FLinks["$ReturnValue"];
        }
    }



    function getFullStreetTypeCode($StreetTypeCode)
    {
        $sql =  "SELECT * FROM `StreetType` WHERE StreetTypeCode like '".$StreetTypeCode."' ;";
        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);
        if (strlen( $FLinks["StreetTypeCode"])>0 )
        {
            return $FLinks["Description"];
        } else
        {
            return $StreetTypeCode;
        }
    }

    function getFullPremiseTypeCode($PremiseTypeCode)
    {
        $sql =  "SELECT * FROM `PremiseType` WHERE PremiseTypeCode like '".$PremiseTypeCode."' ;";
        // echo $sql."<p>";
        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);

        // echo $FLinks["PremiseTypeCode"]."<p>";

        if (is_null( $FLinks["PremiseTypeCode"]) )
        {
            return $PremiseTypeCode;
        } else
        {
            return $FLinks["PremiseTypeCode"];
        }
    }

    function getFullStateTypeCode($StateTypeCode)
    {
        $sql =  "SELECT * FROM `State` WHERE StateCode like '".$StateTypeCode."' ;";
        //  echo $sql."<p>";

        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);

        // echo "Description = ".strlen($FLinks["Description"])."<p>";

        if (strlen( $FLinks["Description"])>0 )
        {
            //  echo "aa";
            return $FLinks["Description"];
        } else
        {
            return $StateTypeCode;
        }
    }


    function getFormattedMobilePhoneNumber($IID, $delim, $bIndlInternationalCode, $bMouseOverCode=FALSE, $strReplaceIfPrivate = "",$bHidePrivate = TRUE)
    {
        $sql =  "SELECT * FROM $this->individualtablename WHERE IndividualId=$IID;";
        // echo $sql."<p>";
        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);

        // echo "bMouseOverCode ".$bMouseOverCode."<p>";
        // echo "strReplaceIfPrivate ".$strReplaceIfPrivate."<p>";
        // echo "MobilePhonePrivateIndicator ".$FLinks["MobilePhonePrivateIndicator"]."<p>";
        //exit;

        if(strlen(trim($FLinks["MobilePhoneNumber"]))==0 ) {
            Return "";
        }


        $FormattedPhoneNumber = "";

        if ( $bIndlInternationalCode and !is_null( $FLinks["MobilePhoneInternationalCode"]) )
        {
            $FormattedPhoneNumber = "+".$FLinks["MobilePhoneInternationalCode"].$delim.$FLinks["MobilePhoneNumber"];
        }
        if (!$bIndlInternationalCode and !is_null( $FLinks["MobilePhoneNumber"]))
        {
            $FormattedPhoneNumber = $FLinks["MobilePhoneNumber"];

            if ( !@$FormattedPhoneNumber{0} == "0")
            {
                $FormattedPhoneNumber = "0".$FormattedPhoneNumber ;
            }
        }
        if ($FLinks["MobilePhonePrivateIndicator"] == 'Y' and $bHidePrivate){
            $ActualFormattedPhoneNumber = $FormattedPhoneNumber;
            $DisplayFormattedPhoneNumber = $this->hideNumbers($FormattedPhoneNumber);
            if ($bMouseOverCode==TRUE) {
                $DisplayFormattedPhoneNumber = '<a href="#" title="Private Mobile Phone Number :: '.$ActualFormattedPhoneNumber.'">'.$DisplayFormattedPhoneNumber.'</a>';
            }
        }
        else
        {
            $DisplayFormattedPhoneNumber = $FormattedPhoneNumber;
        }
        // echo "bMouseOverCode ".$bMouseOverCode."<p>";
        // echo "strReplaceIfPrivate ".$strReplaceIfPrivate."<p>";
        // echo "MobilePhonePrivateIndicator ".$FLinks["MobilePhonePrivateIndicator"]."<p>";

        if(!$bMouseOverCode and strlen($strReplaceIfPrivate)>0 and $FLinks["MobilePhonePrivateIndicator"] == 'Y' and $bHidePrivate) {
            $DisplayFormattedPhoneNumber = trim($strReplaceIfPrivate);
        }
        return $DisplayFormattedPhoneNumber ;
    }


    function getFormattedHomePhoneNumber($FID, $delim, $bIndlInternationalCode, $bInclStd, $bMouseOverCode=FALSE, $strReplaceIfPrivate = "",$bHidePrivate = TRUE)
    {
        $sql =  "SELECT * FROM $this->familytablename WHERE FamilyId=$FID;";
        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);
        $FormattedPhoneNumber = "";

        //print_r($FLinks);
        if(strlen(trim($FLinks["HomePhoneNumber"]))==0 ) {
            Return "";
        }

        if ( $bIndlInternationalCode and !is_null( $FLinks["HomePhoneInternationalCode"]) )
        {
            $FormattedPhoneNumber = "+".$FLinks["HomePhoneInternationalCode"].$delim.$FLinks["HomePhoneStdCode"].$delim.$FLinks["HomePhoneNumber"];
        }
        if (!$bIndlInternationalCode and $bInclStd )
        {
            if(strlen( $FLinks["HomePhoneStdCode"])>0) {
                $FormattedPhoneNumber = $FLinks["HomePhoneStdCode"].$delim.$FLinks["HomePhoneNumber"];
                if ( !$FLinks["HomePhoneStdCode"]{0} == "0")
                {
                    $FormattedPhoneNumber = "0".$FormattedPhoneNumber ;
                }
            } else {
                $FormattedPhoneNumber = $FLinks["HomePhoneNumber"];
            }


        }
        if (!$bIndlInternationalCode and !$bInclStd and !is_null( $FLinks["HomePhoneNumber"]))
        {
            $FormattedPhoneNumber = $FLinks["HomePhoneNumber"];
        }

        $DisplayFormattedPhoneNumber = $FormattedPhoneNumber;

        if ($FLinks["HomePhoneNumberPrivateIndicator"] == 'Y' and $bHidePrivate){
            $ActualFormattedPhoneNumber = $FormattedPhoneNumber;
            $DisplayFormattedPhoneNumber = $this->hideNumbers($FormattedPhoneNumber);
            if ($bMouseOverCode==TRUE) {
                $DisplayFormattedPhoneNumber = '<a href="#" title="Private Home Phone number :: '.$ActualFormattedPhoneNumber.'">'.$DisplayFormattedPhoneNumber.'</a>';
            }
        } else
        {
            $DisplayFormattedPhoneNumber = $FormattedPhoneNumber;
        }


        if(!$bMouseOverCode and strlen($strReplaceIfPrivate)>0 and $FLinks["HomePhoneNumberPrivateIndicator"] == 'Y' and $bHidePrivate) {
            $DisplayFormattedPhoneNumber = trim($strReplaceIfPrivate);
        }

        return $DisplayFormattedPhoneNumber;
    }


    ///////////////////////////////////////////////////////////////////////////////////////////////////////////

    function getFormattedWorkFaxNumber($IID, $delim, $bIndlInternationalCode, $bInclStd, $bMouseOverCode=FALSE, $strReplaceIfPrivate = "", $bHidePrivate = TRUE)
    {
        $sql =  "SELECT * FROM $this->individualtablename WHERE IndividualId=$IID;";
        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);
        $FormattedPhoneNumber = "";

        //print_r($FLinks);
        if(strlen(trim($FLinks["WorkFaxNumber"]))==0 ) {
            Return "";
        }

        if ( $bIndlInternationalCode and !is_null( $FLinks["WorkFaxInternationalCode"]) )
        {
            $FormattedPhoneNumber = "+".$FLinks["WorkFaxInternationalCode"].$delim.$FLinks["WorkFaxStdCode"].$delim.$FLinks["WorkFaxNumber"];
        }
        if (!$bIndlInternationalCode and $bInclStd )
        {
            if(strlen( $FLinks["WorkFaxStdCode"])>0) {
                $FormattedPhoneNumber = $FLinks["WorkFaxStdCode"].$delim.$FLinks["WorkFaxNumber"];
                if ( !$FLinks["WorkFaxStdCode"]{0} == "0")
                {
                    $FormattedPhoneNumber = "0".$FormattedPhoneNumber ;
                }
            } else {
                $FormattedPhoneNumber = $FLinks["WorkFaxNumber"];

            }
        }
        if (!$bIndlInternationalCode and !$bInclStd and !is_null( $FLinks["WorkFaxNumber"]))
        {
            $FormattedPhoneNumber = $FLinks["WorkFaxNumber"];
        }

        $DisplayFormattedPhoneNumber = $FormattedPhoneNumber;

        if ($FLinks["WorkFaxPrivateIndicator"] == 'Y' and $bHidePrivate ){
            $ActualFormattedPhoneNumber = $FormattedPhoneNumber;
            $DisplayFormattedPhoneNumber = $this->hideNumbers($FormattedPhoneNumber);
            if ($bMouseOverCode==TRUE) {
                $DisplayFormattedPhoneNumber = '<a href="#" title="Private Fax Number :: '.$ActualFormattedPhoneNumber.'">'.$DisplayFormattedPhoneNumber.'</a>';
            }
        }

        if(!$bMouseOverCode and strlen($strReplaceIfPrivate)>0 and $FLinks["WorkFaxPrivateIndicator"] == 'Y' and $bHidePrivate ) {
            $DisplayFormattedPhoneNumber = trim($strReplaceIfPrivate);
        }

        return $DisplayFormattedPhoneNumber;
    }


    ///////////////////////////////////////////////////////////////////////////////////////////////////////////

    function getFormattedHomeFaxNumber($FID, $delim, $bIndlInternationalCode, $bInclStd, $bMouseOverCode=FALSE, $strReplaceIfPrivate = "", $bHidePrivate = TRUE)
    {
        $sql =  "SELECT * FROM $this->familytablename WHERE FamilyId=$FID;";
        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);
        $FormattedPhoneNumber = "";

        //print_r($FLinks);
        if(strlen(trim($FLinks["HomeFaxNumber"]))==0 ) {
            Return "";
        }

        if ( $bIndlInternationalCode and !is_null( $FLinks["HomeFaxInternationalCode"]) )
        {
            $FormattedPhoneNumber = "+".$FLinks["HomeFaxInternationalCode"].$delim.$FLinks["HomeFaxStdCode"].$delim.$FLinks["HomeFaxNumber"];
        }
        if (!$bIndlInternationalCode and $bInclStd)
        {
            if(strlen( $FLinks["HomeFaxStdCode"])>0) {
                $FormattedPhoneNumber = $FLinks["HomeFaxStdCode"].$delim.$FLinks["HomeFaxNumber"];
                if ( !$FLinks["HomeFaxStdCode"]{0} == "0")
                {
                    $FormattedPhoneNumber = "0".$FormattedPhoneNumber ;
                }

            } else {
                $FormattedPhoneNumber = $FLinks["HomeFaxNumber"];

            }


        }
        if (!$bIndlInternationalCode and !$bInclStd and !is_null( $FLinks["HomeFaxNumber"]))
        {
            $FormattedPhoneNumber = $FLinks["HomeFaxNumber"];
        }

        $DisplayFormattedPhoneNumber = $FormattedPhoneNumber;

        if ($FLinks["HomeFaxNumberPrivateIndicator"] == 'Y' and $bHidePrivate ){
            $ActualFormattedPhoneNumber = $FormattedPhoneNumber;
            $DisplayFormattedPhoneNumber = $this->hideNumbers($FormattedPhoneNumber);
            if ($bMouseOverCode==TRUE) {
                $DisplayFormattedPhoneNumber = '<a href="#" title="Home Fax Number :: '.$ActualFormattedPhoneNumber.'">'.$DisplayFormattedPhoneNumber.'</a>';
            }
        }

        if(!$bMouseOverCode and strlen($strReplaceIfPrivate)>0 and $FLinks["HomeFaxNumberPrivateIndicator"] == 'Y' and $bHidePrivate ) {
            $DisplayFormattedPhoneNumber = trim($strReplaceIfPrivate);
        }

        return $DisplayFormattedPhoneNumber;
    }

    //////////////////////////////////////////////////////////////////////////////////////////////////////

    function getFormattedWorkPhoneNumber($IID, $delim, $bIndlInternationalCode, $bInclStd, $bMouseOverCode=FALSE, $strReplaceIfPrivate = "", $bHidePrivate = TRUE)
    {
        $sql =  "SELECT * FROM $this->individualtablename WHERE IndividualId=$IID;";
        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);
        $FormattedPhoneNumber = "";

        //print_r($FLinks);
        if(strlen(trim($FLinks["WorkPhoneNumber"]))==0 ) {
            Return "";
        }

        if ( $bIndlInternationalCode and !is_null( $FLinks["WorkPhoneInternationalCode"]) )
        {
            $FormattedPhoneNumber = "+".$FLinks["WorkPhoneInternationalCode"].$delim.$FLinks["WorkPhoneStdCode"].$delim.$FLinks["WorkPhoneNumber"];
        }
        if (!$bIndlInternationalCode and $bInclStd )
        {
            if(strlen( $FLinks["WorkPhoneStdCode"])>0) {
                $FormattedPhoneNumber = $FLinks["WorkPhoneStdCode"].$delim.$FLinks["WorkPhoneNumber"];

                if ( !$FLinks["WorkPhoneStdCode"]{0} == "0")
                {
                    $FormattedPhoneNumber = "0".$FormattedPhoneNumber ;
                }

            } else {
                $FormattedPhoneNumber = $FLinks["WorkPhoneNumber"];

            }

        }
        if (!$bIndlInternationalCode and !$bInclStd and !is_null( $FLinks["WorkPhoneNumber"]))
        {
            $FormattedPhoneNumber = $FLinks["WorkPhoneNumber"];
        }

        $DisplayFormattedPhoneNumber = $FormattedPhoneNumber;

        if ($FLinks["WorkPhonePrivateIndicator"] == 'Y' and $bHidePrivate){
            $ActualFormattedPhoneNumber = $FormattedPhoneNumber;
            $DisplayFormattedPhoneNumber = $this->hideNumbers($FormattedPhoneNumber);
            if ($bMouseOverCode==TRUE) {
                $DisplayFormattedPhoneNumber = '<a href="#" title="Private Work Number :: '.$ActualFormattedPhoneNumber.'">'.$DisplayFormattedPhoneNumber.'</a>';
            }
        }

        if(!$bMouseOverCode and strlen($strReplaceIfPrivate)>0 and $FLinks["WorkPhonePrivateIndicator"] == 'Y' and $bHidePrivate) {
            $DisplayFormattedPhoneNumber = trim($strReplaceIfPrivate);
        }

        return $DisplayFormattedPhoneNumber;
    }

    //////////////////////////////////////////////////////////////////////////////////////////////////////

    function getFormattedResidentialAddress($FID, $delim, $bUseAbbrevCodes, $bInclCountry, $bExclAUCountry, $bFirstLineOnly = FALSE, $bMouseOverCode=FALSE, $strReplaceIfPrivate = "", $bHidePrivate = TRUE, $bHidePropName = FALSE,$bShowState = TRUE,$strLotDelim = ",", $bReplaceHtmlSpecialChars = FALSE)
    {

        /*
                $FID is the FamilyId for the address
                $delime is the delimited to insert between lines (e.g. <br />, \n)
                $bUseAbbrevCodes If TRUE, then we use the abbrev Codes (ie what is in the Family table), if FALSE we must resolve the names against the lookup tables.
        */

        // echo "Delim = $delim, char =".strlen($delim)."<p>";


        // for ($x=0;$x<strlen($delim);$x++) {
        //  echo " :$x : ".(int)$delim[$x]."<br />";
        // }

        $CompiledAddress = "";
        $DisplayCompiledAddress="";

        $fResult = mysql_query("SELECT * FROM ".$this->familytablename." WHERE FamilyId=$FID;",$this->dblink);
        //

        //   $result = mysql_query("SELECT * FROM $individual WHERE IndividualId=$id ",$this->dblink);
        //   $result = mysql_query("SELECT * FROM $table ",$this->dblink);

        $FLinks = mysql_fetch_array($fResult);
        $RPropName = $FLinks["ResidentialPropertyName"];
        $RFloor = $FLinks["ResidentialFloor"];
        $RPremNum = $FLinks["ResidentialPremiseNum"];
        $RPremType = $FLinks["ResidentialPremiseType"];
        $RStrNum = $FLinks["ResidentialStreetNum"];
        $RStrNme = $FLinks["ResidentialStreetName"];
        $RStrTypeCde = $FLinks["ResidentialStreetTypeCode"];
        $RSuburb = $FLinks["ResidentialSuburb"];
        $RPstcd = $FLinks["ResidentialPostcode"];
        $RSteCde = $FLinks["ResidentialStateCode"];
        $RCountry = $FLinks["ResidentialCountry"];

        if (strlen(trim($RStrNme.$RSuburb.$RPstcd))==0){
            return "";
        }


        // now we have this, do we resolve abbreviated codes?
        if (!$bUseAbbrevCodes)
        {
            //  echo "using full codes<p>";
            //  echo "$RStrTypeCde :: ".getFullStreetTypeCode($RStrTypeCde)."<p>";
            //  echo "$RPremType   :: ".getFullPremiseTypeCode($RPremType)."<p>";
            //  echo "$RSteCde :: ".getFullStateTypeCode($RSteCde)."<p>";
            $RStrTypeCde = getFullStreetTypeCode($RStrTypeCde);
            $RPremType   = getFullPremiseTypeCode($RPremType);
            $RSteCde     = getFullStateTypeCode($RSteCde);
            $floor = "Floor";
        } else {
            $floor = "Fl";
        }


        //echo "RStrTypeCde = $RStrTypeCde<p>";
        if (strlen(trim($RPropName))>0 AND $bHidePropName==FALSE) {
            //  echo "....$RPropName.......<p>";
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RPropName).$delim;
                $CompiledAddress = $CompiledAddress.$RPropName."\n";
            } else
            {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($RPropName).$delim;
                } else {
                    $CompiledAddress = $CompiledAddress.$RPropName.$delim;
                }
            }
        };
        if (strlen(trim($RFloor))>0) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RFloor)." ";
                $CompiledAddress = $CompiledAddress.$floor." ".$RFloor.$strLotDelim." ";
            } else {
                $CompiledAddress = $CompiledAddress.$floor." ".$RFloor.$strLotDelim." ";
            }
        };
        if (strlen(trim($RPremType))>0 and strlen(trim(($RPremNum)))>0) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RPremType." ".$RPremNum)." ";
                $CompiledAddress = trim($CompiledAddress).trim($RPremType." ".$RPremNum).$strLotDelim." ";
            } else {
                $CompiledAddress = trim($CompiledAddress).trim($RPremType." ".$RPremNum).$strLotDelim." ";

            }
        };
        if ((!strlen(trim($RPremType))>0) and strlen($RPremNum)>0) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RPremNum."/");
                $CompiledAddress = $CompiledAddress.$RPremNum."/";
            } else {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($RPremNum."/");
                } else {

                    $CompiledAddress = $CompiledAddress.$RPremNum."/";
                }
            }
        };
        if (!is_null($RStrNum)) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RStrNum." ");
                $CompiledAddress = $CompiledAddress.$RStrNum." ";
            } else {
                $CompiledAddress = $CompiledAddress.$RStrNum." ";

            }
        };
        if (!is_null($RStrNme)) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RStrNme)." ";
                $CompiledAddress = $CompiledAddress.$RStrNme." ";
            } else {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($RStrNme." ");
                } else {
                    $CompiledAddress = $CompiledAddress.$RStrNme." ";

                }
            }
        };
        // if (!is_null($RStrTypeCde)) {
        if (TRUE) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RStrTypeCde).$delim;
                $CompiledAddress = $CompiledAddress.$RStrTypeCde."\n";
            } else {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($RStrTypeCde).$delim;
                } else {
                    $CompiledAddress = $CompiledAddress.$RStrTypeCde.$delim;
                }
            }
        };
        if (!is_null($RSuburb) AND !$bFirstLineOnly) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RSuburb).$delim;
                $CompiledAddress = $CompiledAddress.$RSuburb."\n";
            } else {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($RSuburb).$delim;
                } else {
                    $CompiledAddress = $CompiledAddress.$RSuburb.$delim;
                }
            }
        };
        if (!is_null($RSteCde) AND !$bFirstLineOnly AND $bShowState) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RSteCde."     ");
                $CompiledAddress = $CompiledAddress.$RSteCde."     ";
            } else {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($RSteCde).$delim;
                } else {
                    $CompiledAddress = $CompiledAddress.$RSteCde."     ";
                }
            }
        };
        if (!is_null($RPstcd) AND !$bFirstLineOnly) {
            if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RPstcd);
                $CompiledAddress = $CompiledAddress.$RPstcd;
            } else {
                $CompiledAddress = $CompiledAddress.$RPstcd;
            }
        };
        if (!is_null($RCountry AND !$bFirstLineOnly) and $bInclCountry)
        {
            if ($bExclAUCountry and $RCountry != "Australia" )
            {
                $CompiledAddress = $CompiledAddress.$RCountry;
                if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                    $CompiledAddress = $CompiledAddress."\n";
                    $DisplayCompiledAddress = $DisplayCompiledAddress.$delim;
                    $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RCountry);
                } else {
                    $CompiledAddress = $CompiledAddress.$delim;
                    $DisplayCompiledAddress = $DisplayCompiledAddress.$delim;
                }
            }
            else
            {
                if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
                    $CompiledAddress = $CompiledAddress.$delim.$RCountry;
                    $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($RCountry);
                } else {
                    if ($bReplaceHtmlSpecialChars){
                        $CompiledAddress = $CompiledAddress."\n".htmlspecialchars($RCountry);
                    } else {
                        $CompiledAddress = $CompiledAddress."\n".$RCountry;
                    }
                }
            }
        }

        // echo "<p>DisplayCompiledAddress : $DisplayCompiledAddress<p>";


        //  $DisplayCompiledAddress = $CompiledAddress;

        if ($FLinks["ResidentialAddressPrivateIndicator"] == 'Y' AND $bHidePrivate){
            if ($bMouseOverCode==TRUE) {
                $tmp = '<a href="#" title="Private Address :: ';
                $tmp = $tmp ."\n";
                $tmp = $tmp.$CompiledAddress.'">'.$DisplayCompiledAddress.'</a>';
                $DisplayCompiledAddress  = $tmp ;
            }
        } else
        {
            $DisplayCompiledAddress = $CompiledAddress;
        }

        if(!$bMouseOverCode and strlen($strReplaceIfPrivate)>0 and $FLinks["ResidentialAddressPrivateIndicator"] == 'Y') {
            $DisplayCompiledAddress = trim($strReplaceIfPrivate);
        }

        //echo "<p>END<p><pre>DisplayCompiledAddress : ".htmlentities($DisplayCompiledAddress)."</pre><p>";
        return $DisplayCompiledAddress;
    }// end getFormattedResidentialAddress()


    //////////////////////////////////////////////////////////////////////////////////////////////////////

    function getFormattedPostalAddress($FID, $delim, $bUseAbbrevCodes, $bInclCountry, $bExclAUCountry, $bMouseOverCode=FALSE, $strReplaceIfPrivate = "", $bReplaceHtmlSpecialChars = FALSE)
    {
        /*
                $FID is the FamilyId for the address
                $delime is the delimited to insert between lines (e.g. <br />, \n)
                $bUseAbbrevCodes If TRUE, then we use the abbrev Codes (ie what is in the Family table), if FALSE we must resolve the names against the lookup tables.
        */

        $fResult = mysql_query("SELECT * FROM $this->familytablename WHERE FamilyId=$FID;",$this->dblink);
        //

        //   $result = mysql_query("SELECT * FROM $individual WHERE IndividualId=$id ",$this->dblink);
        //   $result = mysql_query("SELECT * FROM $table ",$this->dblink);

        $FLinks = mysql_fetch_array($fResult);

        $PAddrLn1 = $FLinks["PostalAddressLine1"];
        $PAddrLn2 = $FLinks["PostalAddressLine2"];
        $PAddrSuburb = $FLinks["PostalAddressSuburb"];
        $PAddrPstcd = $FLinks["PostalAddressPostcode"];
        $PAddrSteCde = $FLinks["PostalAddressStateCode"];
        $PAddrCountry = $FLinks["PostalAddressCountry"];
        $PAddrPrivInd = "N"; // set default
        $PAddrPrivInd = $FLinks["PostalAddressPrivateIndicator"];

        $CompiledAddress = "";
        $DisplayCompiledAddress="";



        if (strlen(trim($PAddrLn1.$PAddrLn2.$PAddrSuburb))){
            return "";
        }


        // now we have this, do we resolve abbreviated codes?
        if (!$bUseAbbrevCodes)
        {
            $PAddrSteCde     = getFullStateTypeCode($PAddrSteCde);
        }


        //

        if (!is_null($PAddrLn1) and strlen($PAddrLn1)>0) {


            if ($PAddrPrivInd == 'Y'){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($PAddrLn1,$strReplaceIfPrivate).$delim;
                $CompiledAddress = $CompiledAddress.$PAddrLn1."\n";
            } else
            {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = htmlspecialchars($PAddrLn1).$delim;
                } else {
                    $CompiledAddress = $PAddrLn1.$delim;
                }
            }

        };
        if (!is_null($PAddrLn2)) {
            if ($PAddrPrivInd == 'Y'){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($PAddrLn2,$strReplaceIfPrivate).$delim;
                $CompiledAddress = $CompiledAddress.$PAddrLn2."\n";
            } else
            {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($PAddrLn2).$delim;
                } else {
                    $CompiledAddress = $CompiledAddress.$PAddrLn2.$delim;
                }
            }
        };
        if (!is_null($PAddrSuburb)) {
            if ($PAddrPrivInd == 'Y'){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($PAddrSuburb,$strReplaceIfPrivate).$delim;
                $CompiledAddress = $CompiledAddress.$PAddrSuburb."\n";
            } else
            {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($PAddrSuburb).$delim;
                } else {
                    $CompiledAddress = $CompiledAddress.$PAddrSuburb.$delim;
                }
            }
        };
        if (!is_null($PAddrSteCde)) {
            if ($PAddrPrivInd == 'Y'){


                //fix error
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($PAddrSteCde."     ",$strReplaceIfPrivate);
                $CompiledAddress = $CompiledAddress.$PAddrSteCde."     ";
            } else
            {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($PAddrSteCde)."     ";
                } else {
                    $CompiledAddress = $CompiledAddress.$PAddrSteCde."     ";
                }
            }

        };
        if (!is_null($PAddrPstcd)) {
            if ($PAddrPrivInd == 'Y'){
                $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($PAddrPstcd,$strReplaceIfPrivate);
                $CompiledAddress = $CompiledAddress.$PAddrPstcd;
            } else
            {
                if ($bReplaceHtmlSpecialChars){
                    $CompiledAddress = $CompiledAddress.htmlspecialchars($PAddrPstcd);
                } else {
                    $CompiledAddress = $CompiledAddress.$PAddrPstcd;
                }
            }

        };
        if (!is_null($PAddrCountry) and $bInclCountry)
        {
            $CompiledAddress = $CompiledAddress.$delim;
            if ($bExclAUCountry and $PAddrCountry != "Australia" )
            {
                if ($PAddrPrivInd == 'Y'){
                    $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($PAddrCountry,$strReplaceIfPrivate);
                    $CompiledAddress = $CompiledAddress.$PAddrCountry;
                } else
                {
                    if ($bReplaceHtmlSpecialChars){
                        $CompiledAddress = $CompiledAddress.htmlspecialchars($PAddrCountry);
                    } else {
                        $CompiledAddress = $CompiledAddress.$PAddrCountry;
                    }
                }
            }
            else
            {
                if ($PAddrPrivInd == 'Y'){
                    $DisplayCompiledAddress = $DisplayCompiledAddress.$this->hideChars($PAddrCountry,$strReplaceIfPrivate);
                    $CompiledAddress = $CompiledAddress.$PAddrCountry;
                } else
                {
                    if ($bReplaceHtmlSpecialChars){
                        $CompiledAddress = $CompiledAddress.htmlspecialchars($PAddrCountry);
                    } else {
                        $CompiledAddress = $CompiledAddress.$PAddrCountry;
                    }
                }
            }
        }

        //$rr = LogData3NEW("1Com:".$CompiledAddress );
        //$rr = LogData3NEW("1dis:".$DisplayCompiledAddress  );


        if ($PAddrPrivInd == 'Y'){
            if ($bMouseOverCode==TRUE) {
                $tmp = '<font title="Private Postal Address :: ';
                $tmp = $tmp ."\n";
                $tmp = $tmp.$CompiledAddress.'">'.$DisplayCompiledAddress.'</font>';
                $DisplayCompiledAddress  = $tmp ;
            }
        } else
        {
            $DisplayCompiledAddress = $CompiledAddress;
        }


        //$rr = LogData3NEW("2Com:".$CompiledAddress );
        //$rr = LogData3NEW("2dis:".$DisplayCompiledAddress  );
        //$rr = LogData3NEW("strReplaceIfPrivate :".$strReplaceIfPrivate."  ".strlen($strReplaceIfPrivate) );

        if(!$bMouseOverCode and strlen($strReplaceIfPrivate)>0 and $PAddrPrivInd == 'Y') {
            $DisplayCompiledAddress = trim($strReplaceIfPrivate);
            //  $rr = LogData3NEW("%%%%%%%%%%%%%%%%%%%%%%%%");
        }

        // print  " DisplayCompiledAddress = $DisplayCompiledAddress<br />";
        // print  " CompiledAddress = $CompiledAddress<br />";
        // print  " PAddrPrivInd = $PAddrPrivInd <br />";

        //$rr = LogData3NEW("3Com:".$CompiledAddress );
        //$rr = LogData3NEW("3dis:".$DisplayCompiledAddress  );

        return trim($DisplayCompiledAddress);
    }


    //////////////////////////////////////////////////////////////////////////////////////////////////////




    function getStatisticsTable( $result, $StatisticName,$bIgnoreBlankEntries = FALSE ,$strBlankValue="[blank]", $resultBlank="") {
        $strHTMLtable = "";

        $row = mysql_fetch_assoc($result);

        if(!is_null($row)){
            $strHTMLtable .= '<div class="viewDetailsHeaderB">'; //viewDetailsHeaderB Statistic_title
            $strHTMLtable .= $StatisticName;
            $strHTMLtable .= '</div>'."\n";
            $strHTMLtable .= '<table border="0" class="Statistic">'."\n";
            $strHTMLtable .= '<tr>'."\n";


            foreach($row as $key=>$val) {
                $strHTMLtable .= '<td class="viewDetailsHeader">'."\n";//viewDetailsHeader
                $strHTMLtable .= htmlspecialchars($key);
                $strHTMLtable .= '</td>'."\n";
            }
            $strHTMLtable .= '</tr>'."\n";

            do{
                //print_r($row);
                //echo "--<p>";
                $KeyWasBlank = TRUE;
                $strHTMLtable .= "<tr>\n";
                foreach($row as $key=>$val) {
                    $val_o = $val;
                    $key_o = $key;

                    if(strlen($val)==0 and !$bIgnoreBlankEntries) {
                        $val = $strBlankValue;
                    }
                    if((strlen($val_o)>0) or (($bIgnoreBlankEntries and strlen($val_o)==0))) {
                        $strHTMLtable .= "<td class=\"Statistic_data\">";
                        $strHTMLtable .= htmlentities($val);
                        $strHTMLtable .= "</td>\n";
                    }
                    else
                    {
                        $KeyWasBlank = TRUE;
                        $strHTMLtable .= "<td class=\"Statistic_data\">";
                        //     $strHTMLtable .= $val;
                        $strHTMLtable .= "</td>\n";

                    }

                }
                $strHTMLtable .= "</tr>\n";
                $row = mysql_fetch_assoc($result );
            } while($row);

            if(@strlen($resultBlank)>0) {

                $strHTMLtable .= "<tr>\n";
                $strHTMLtable .= "<td class=\"Statistic_data\">blank</td>";


                $row_blank = mysql_fetch_assoc($resultBlank);
                $blankCount= $row_blank['Count(*)'];
                // print_r($row_blank );
                $strHTMLtable .= "<td class=\"Statistic_data\">$blankCount</td>";
                $strHTMLtable .= "</tr>\n";

            }


            $strHTMLtable .= "</table>\n";

        }
        return $strHTMLtable;
    }




    function getEmailAddress($IID, $Type, $bMouseOverCode=FALSE, $strReplaceIfPrivate = "",$bHidePrivate = TRUE )
    {
        $sql =  "SELECT * FROM $this->individualtablename WHERE IndividualId=$IID;";
        // echo $sql."<p>";
        $fResult = mysql_query($sql,$this->dblink);
        $FLinks = mysql_fetch_array($fResult);

        // echo "bMouseOverCode ".$bMouseOverCode."<p>";
        // echo "strReplaceIfPrivate ".$strReplaceIfPrivate."<p>";
        // echo "MobilePhonePrivateIndicator ".$FLinks["MobilePhonePrivateIndicator"]."<p>";
        //exit;

        switch (strtoupper($Type)) {
            case "HOME":
                $em=$FLinks["HomeEmailAddress"];
                $pv=$FLinks["HomeEmailAddressPrivateIndicator"];
                break;
            case "WORK":
                $em=$FLinks["WorkEmailAddress"];
                $pv=$FLinks["WorkEmailAddressPrivateIndicator"];
                break;
            default:
                return;
            }

            if($pv=='Y' and $bHidePrivate) {
                $em = $this->hideChars($em);
            }
            return $em;
        }

/*
*/



        Function getHTMLtable($sql, $bHeadings = TRUE){
            // global $content2_mouseover;

            $strHTML = "";
            $strValues = "";
            // $query = "SELECT * FROM `geo_contacts` limit 0, 5";
            $result = mysql_query($sql,$this->dblink) or die("Query failed : " . mysql_error());

    /* Printing results in HTML */
            $strHTML.= "<table border=1 >\n";
            while ($line = mysql_fetch_assoc($result)) {
                $strValues = "";
                $strHTML.= "\t<tr>\n";
                foreach ($line as $key=>$col_value) {
                    if($bHeadings==TRUE) {
                        $strHTML.= "<td class=\"smalltxt\"><b>$key</b></td>\n";
                    }
                    $strValues.= "<td class=\"smalltxt\">$col_value&nbsp;</td>\n";
                }
                if($bHeadings==TRUE) {
                    $strHTML.= "\t</tr><tr>\n";

                }
                $bHeadings=FALSE;

                $strHTML.= $strValues."\t</tr>\n";
            }
            $strHTML.="</table>\n";
            return $strHTML;
        }

        function LogQuery($somecontent,$line,$file)
        {

            //$somecontent = $somecontent."\n";
            $filename = '/home/colin/wordpress-2.6.5-0/apps/pacts/htdocs/log.txt';
/*
        $datetime = date("d/m/Y")." ".date("H:i:s");
        // Let's make sure the file exists and is writable first.
        if (is_writable($filename)) {
                // In our example we're opening $filename in append mode.
                // The file pointer is at the bottom of the file hence
                // that's where $somecontent will go when we fwrite() it.
                if (!$handle = fopen($filename, 'a')) {
//    print "Cannot open file ($filename)";
                         exit;
                }

                // Write $somecontent to our opened file.
                if (!fwrite($handle, $datetime." ".$file."(".$line."): ".$somecontent)) {
//   print "Cannot write to file ($filename)";
                        exit;
                }

//  print "Success, wrote ($somecontent) to file ($filename)";

                fclose($handle);

        } else {
//  print "The file $filename is not writable";
        }
*/
            return mysql_query($somecontent,$this->dblink);

        }


        function LogData($somecontent)
        {
/*
        $somecontent = $somecontent."\n";
        $filename = 'e:\page_Test_query.txt';
        $datetime = date("d/m/Y")." ".date("H:i:s");

        // Let's make sure the file exists and is writable first.
        if (is_writable($filename)) {
                if (!$handle = fopen($filename, 'a')) {
                         exit;
                }

                // Write $somecontent to our opened file.
                if (!fwrite($handle, $datetime." ".$somecontent)) {
                        exit;
                }
        fclose($handle);
        }
*/
            return;
        }
        function LogData2($somecontent)
        {
/*
        $somecontent = $somecontent."\n";
        $filename = 'e:\page_Test_query2.txt';
        $datetime = date("d/m/Y")." ".date("H:i:s");

        // Let's make sure the file exists and is writable first.
        if (is_writable($filename)) {
                if (!$handle = fopen($filename, 'a')) {
                         exit;
                }

                // Write $somecontent to our opened file.
                if (!fwrite($handle, $datetime." ".$somecontent)) {
                        exit;
                }
        fclose($handle);
        }
*/
            return;
        }


        function LogData3NEW($somecontent)
        {
/*
        $somecontent = $somecontent."\n";
        $filename = 'e:\logdata3NEW.txt';
        $datetime = date("d/m/Y")." ".date("H:i:s");

        // Let's make sure the file exists and is writable first.
        if (is_writable($filename)) {
                if (!$handle = fopen($filename, 'a')) {
                         exit;
                }

                // Write $somecontent to our opened file.
                if (!fwrite($handle, $datetime." ".$somecontent)) {
                        exit;
                }
        fclose($handle);
        }
*/
            return;
        }


        Function printFooterText($pdf, $OriginalPageNumber, $intPageXMidX , $font , $fontsize,$intPageCountPosY, $bShowPageFooter = TRUE ,$bShowPageNumber = TRUE, $bDebugOn = FALSE){
            //////////////////////////////////////////////////////
            // Retreive and print Footer details
            // i.e. Footer message (from table BookletFooterMessage - use Maintain RefData page to update)
            // and Page Numbers

            //global $FontCharCountToSizeRatio,$intPageCountPosY,$intMinBottomPageY;

            if ($bDebugOn){ echo "<p> printFooterText </p>"; }


            if ($bShowPageFooter == 'TRUE'){
                // retrive and print page footers
                $result_FooterMsg_MessageText = "";
                $sql_FooterMsg = "SELECT MessageTextLine1,MessageTextLine2,MessageTextLine3 FROM ".$this->bookletfootermessagetablename." WHERE PageNo=".$OriginalPageNumber.";";

                $result_FooterMsg= $this->LogQuery($sql_FooterMsg,__LINE__,__FILE__ );
                $row_FMsg = mysql_fetch_array($result_FooterMsg);
                $result_FooterMsg_MessageTextLine1 = trim($row_FMsg["MessageTextLine1"]);
                $result_FooterMsg_MessageTextLine2 = trim($row_FMsg["MessageTextLine2"]);
                $result_FooterMsg_MessageTextLine3 = trim($row_FMsg["MessageTextLine3"]);

                if ($bDebugOn){ echo "<p> result_FooterMsg_MessageTextLine1 : ".$result_FooterMsg_MessageTextLine1." : x = ".$intPageXMidX-pdf_stringwidth ( $pdf, $result_FooterMsg_MessageTextLine1,$font,$fontsize)*$this->FontCharCountToSizeRatio.", y= ".($intPageCountPosY+35)."</p>"; }
                if ($bDebugOn){ echo "<p> result_FooterMsg_MessageTextLine2 : ".$result_FooterMsg_MessageTextLine2." : x = ".$intPageXMidX-pdf_stringwidth ( $pdf, $result_FooterMsg_MessageTextLine2,$font,$fontsize)*$this->FontCharCountToSizeRatio.", y= ".($intPageCountPosY+24)."</p>"; }
                if ($bDebugOn){ echo "<p> result_FooterMsg_MessageTextLine3 : ".$result_FooterMsg_MessageTextLine3." : x = ".$intPageXMidX-pdf_stringwidth ( $pdf, $result_FooterMsg_MessageTextLine3,$font,$fontsize)*$this->FontCharCountToSizeRatio.", y= ".($intPageCountPosY+13)."</p>"; }

                if (!$bDebugOn){
                    pdf_show_xy($pdf, "$result_FooterMsg_MessageTextLine1", $intPageXMidX-pdf_stringwidth ( $pdf, $result_FooterMsg_MessageTextLine1,$font,$fontsize)*$this->FontCharCountToSizeRatio  , $intPageCountPosY+35);
                    pdf_show_xy($pdf, "$result_FooterMsg_MessageTextLine2", $intPageXMidX-pdf_stringwidth ( $pdf, $result_FooterMsg_MessageTextLine2,$font,$fontsize)*$this->FontCharCountToSizeRatio  , $intPageCountPosY+24);
                    pdf_show_xy($pdf, "$result_FooterMsg_MessageTextLine3", $intPageXMidX-pdf_stringwidth ( $pdf, $result_FooterMsg_MessageTextLine3,$font,$fontsize)*$this->FontCharCountToSizeRatio  , $intPageCountPosY+13);
                }
            }

            if ($bShowPageNumber == 'TRUE' && !$bDebugOn){
                //Show Page Number

                pdf_show_xy($pdf, "Page $OriginalPageNumber", $intPageXMidX-pdf_stringwidth ( $pdf, "Page $OriginalPageNumber" ,$font,$fontsize)*$this->FontCharCountToSizeRatio   , $intPageCountPosY);

            }

            return;
        }

/*

        function check_login(){


            if (@$_SESSION['logged'] == 1) { // User is already logged in.

                return true;


            }
            else {
                if (!isset($_POST['submit'])) { // The form has not been submitted.
                    echo "XXXXXXXXXXXYYYYYYYYYYYYYYYY<form action=\"LoginUser.php\" method=\"POST\">";
                    echo "<table>";
                    echo "<tr>";
                    echo "<td colspan=\"2\">Login:</td>";
                    echo "</tr>";
                    echo "<tr>";
                    echo "<td width=\"50%\">Username:</td><td width=\"50%\"><input name=\"username\" size=\"18\" type=\"text\" />";
                    echo "</tr>";
                    echo "<tr>";
                    echo "<td width=\"50%\">Password:</td><td width=\"50%\"><input name=\"password\" size=\"18\" type=\"text\" />";
                    echo "</tr>";
                    echo "<tr>";
                    echo "<td colspan=\"2\"><input type=\"submit\" name=\"submit\" value=\"submit\"</td>";
                    echo "</tr>";
                    echo "</table>";
                    echo "</form>";
                } else {
                    $username = $this->cleanformdata($_POST['username']);
                    $password = md5($_POST['password']); // Encrypts the password.

                    $q = mysql_query("SELECT * FROM `users` WHERE username = '$username' AND pw = '$password'",$this->dblink) or die (mysql_error()); // mySQL query
                    $r = mysql_num_rows($q); // Checks to see if anything is in the db.

                    if ($r == 1) { // There is something in the db. The username/password match up.
                        $_SESSION['logged'] = 1; // Sets the session.
                        header("Location: Home.php"); // Goes to main page.
                        exit(); // Stops the rest of the script.
                    } else { // Invalid username/password.
                        return false;
                        exit("Incorrect username/password!"); // Stops the script with an error message.
                    }
                }
            }
            mysql_close($this->dblink); // Closes the connection.


        }

*/
        function cleanformdata($data) {
            //// Prevents SQL Injection
            if (strlen(@$data) > 0){
                $data = ereg_replace("[\'\")(;|`,<>]", "", $data);
                if ($this->dblink == null){
                    $this->ConnectToDatabase();
                }
                $data = mysql_real_escape_string(trim($data), $this->dblink);


                $data = stripslashes($data);
            }
            return $data;
        }



        public function addBufferedContent($str)
        {
            if ($this->use_buffered_content){
                //        echo "using buffering".strlen($this->buffered_content)."<br />";
                $this->buffered_content = $this->buffered_content.$str;
            } else {
                //        echo "NOT using buffering".strlen($str)."<br />";
                echo $str;
            }
        }

        public function getBufferedContent()
        {
            $content = $this->buffered_content;
            $this->buffered_content = "";
            return $content;
        }



        function print_ar($array,$return_me=false){
            $return="";
            if(is_array($array) == false){
                $return = "The provided variable is not an array.";
            }else{
                foreach($array as $name=>$value){
                    if(is_array($value)){
                        $return .= "";
                        $return .= "['<b>$name</b>'] {<div style='margin-left:10px;'>\n";
                        $return .= $this->print_ar($value,true);
                        $return .= "</div>}";
                        $return .= "\n\n";
                    }else{
                        if(is_string($value)){
                            $value = "\"$value\"";
                        }
                        $return .= "['<b>$name</b>'] = $value\n";
                    }
                }
            }
            if($return_me == true){
                return $return;
            }else{
                echo "<pre>".$return."</pre>";
            }
        }

        function print_login_form(){
            //print login form
            echo '<form action="login.php" name="Login" method="POST">'."\n";
            echo '<table>'."\n";
            echo '<tr>'."\n";
            echo '<td colspan="2"><h2>Login:</h2></td>'."\n";
            echo '</tr>'."\n";
            echo '<tr>'."\n";
            echo '<td width="50%">Username:</td><td width="50%"><input name="username" size="18" type="text" />'."\n";
            echo '</tr>'."\n";
            echo '<tr>'."\n";
            echo '<td width="50%">Password:</td><td width="50%"><input name="password" size="18" type="password" />'."\n";
            echo '</tr>'."\n";
            echo '<tr>'."\n";
            echo '<td colspan="2"><input type="submit" name="submit" value="Login"</td>'."\n";
            echo '</tr>'."\n";
            echo '</table>'."\n";
            echo '</form>'."\n";
            // set focus on username field
            echo '<script type="text/javascript">'."\n";
            echo 'document.Login.username.focus();'."\n";
            echo '</script>'."\n";
        }


        function remove_duplicated_amps($stringtofix){
            $stringtoremove = "&amp;&amp;";
            $stringtoreplacewith = "&amp;";
            $compiledstring = str_replace($stringtoremove, $stringtoreplacewith, $stringtofix);
            return $compiledstring ;
        }

        function doesTableExist($tablename){
            $sql = 'desc '.$tablename;
            //echo "[doesTableExist] : $tablename. sql = '$sql'<br>";
            $result = mysql_query($sql,$this->dblink);
            return $result;
        }




        function createTable($tablename){
            //open file
            $sql = $this->getSqlFromFile("_setup/_sql/",$tablename);
            $result = mysql_query($sql,$this->dblink);
            return $result;
        }
        function getSqlFromFile($folder, $tablename){
            $myFile = $folder.$tablename.".sql";
            $fh = fopen($myFile, 'r');
            $sql = fread($fh, filesize($myFile));
            fclose($fh);
            return $sql;
        }
        function checkTableStructure($tablename,$bMakeChanges = false){

            $bTableStructureOk = true;
            $sql = $this->getSqlFromFile("_setup/_sql/",$tablename);
            $tablename_tmp = "_tmp";
            //drop tmp table
            try{

                $result = mysql_query("DROP TABLE IF EXISTS `_tmp`;",$this->dblink);
                if (!$result){
                    //throw new Exception("Could not drop table _tmp. Make sure database user has sufficent privileges to create/drop tables");
                }

                $search = "CREATE TABLE IF NOT EXISTS `".$tablename."` (";
                $replace = "CREATE TABLE IF NOT EXISTS `".$tablename_tmp."` (";

                $sql_r = str_replace($search, $replace, $sql);
                //echo $sql_r;
                // create $sql_r table (as temp)
                $result = mysql_query($sql_r,$this->dblink);

                if (!$result){
                    throw new Exception("Could not create table ".$tablename_tmp.". Make sure database user has sufficent privileges to create/drop tables");
                }

                //compare columns against real table
                $bTableStructureOk = $this->checkTableColumns($tablename_tmp,$tablename,$bMakeChanges);
                // create columns where required

                //drop tmp table
                $result = mysql_query("DROP TABLE IF EXISTS `_tmp`;",$this->dblink);
                if (!$result){
                    throw new Exception("Could not drop table _tmp. Make sure database user has sufficent privileges to create/drop tables");
                }


/* 1. read sql.
   2. replace tablename with _tmp
 * 3. create _tmp
 * 4. check columns against real table
 * 5. create/alter missing columns (do not delete)
 * 6. delete _tmp
*/
            } catch (Exception $e){
                //$this->error500("Error while checking table columns ".$e->getMessage());
            }
            return $bTableStructureOk;
        }

        function checkTableColumns($sourcetable,$targettable,$bMakeChanges = false){
            // for each column in source check it is in target
            $bTableStructureOk = true;

            $result_source = mysql_query("desc ".$sourcetable,$this->dblink);

            while ($row_source = mysql_fetch_array($result_source)) {
                $row_source_fieldname = $row_source['Field'];
                $sql_target_field = "show columns from ".$targettable." where Field = '".$row_source_fieldname."'";
                //echo $sql_target_field.__LINE__."<p>";
                $result_target = mysql_query($sql_target_field ,$this->dblink);
                $row_target = mysql_fetch_array($result_target);
                $columnIncorrect =false;

                //work out null options
                if ($row_source['Null'] == "NO"){
                    $null_option = " NOT NULL ";
                } else {
                    $null_option = " NULL ";
                }

                //work out default options
                if ($row_source['Default'] != ""){
                    $default_option = " default '".$row_source['Default']."'";
                } else {
                    if ($null_option == " NULL "){
                        $default_option = " default NULL ";
                    } else {
                        $default_option = "default ''";

                    }
                }


                if (!mysql_num_rows($result_target)>0)
                {
                    //columns is missing
                    echo 'Table <b>'.$targettable.'</b>, Column <b>'.$row_source['Field'].'</b> is incorrect'."\n";
                    $bTableStructureOk = false;
                    // add column

                    if ($bMakeChanges){
                        $sql_add_column ="ALTER TABLE ".$targettable." ADD ".$row_source['Field']." ".$row_source['Type']." ".$null_option." ".$default_option;
                        //echo $sql_add_column."<br>";

                        if (mysql_query($sql_add_column,$this->dblink)){
                            echo " Column added";
                            $bTableStructureOk = true;
                        }
                    }
                    echo "<br>";

                }
                else{


                    if ($row_target['Type'] != $row_source['Type']){
                        //type is incorrect
                        echo "Type is incorrect<br>";
                        $columnIncorrect =true;
                    }
                    if ($row_target['Null'] != $row_source['Null']){
                        //null is incorrect
                        echo "Null is incorrect<br>";
                        $columnIncorrect =true;
                    }
                    if ($row_target['Default'] != $row_source['Default']){
                        //Default is incorrect
                        echo "Default is incorrect<br>";
                        $columnIncorrect =true;
                    }
                    if ($row_target['Extra'] != $row_source['Extra']){
                        //Extra is incorrect
                        echo "Extra is incorrect<br>";
                        $columnIncorrect =true;
                    }
                    if ($columnIncorrect ){
                        $bTableStructureOk = false;
                        if ($bMakeChanges){
                            echo 'Table <b>'.$targettable.'</b>, Column <b>'.$row_source['Field'].'</b> is incorrect'."\n";
                            $sql_modify_column ="ALTER TABLE ".$targettable." MODIFY ".$row_source['Field']." ".$row_source['Type']." ".$null_option." ".$default_option;
                            //echo $sql_modify_column."<br>";
                            if (mysql_query($sql_modify_column,$this->dblink)){
                                echo " Column altered.";
                                $bTableStructureOk = true;
                            }
                        }
                        echo "<br>";

                    }

                }
            }
            return $bTableStructureOk;
        }//end function

        function checkSqlSetup($bMakeChanges = false, $bCheckColumns = false, $bOutputInfo = false)
        {
            $dir = "_setup/_sql";
            $bSqlSetupOk = true;
            $count =0;
            $opened_file = opendir($dir);
            $output = "";
            while ( $file = readdir($opened_file) ) {
                if ( $file != "." && $file != ".." && substr($file, strlen($file)-4, 4) == ".sql") {
                    if (strpos(" ".$file, "phpMyEdit")>0){
                        continue;
                    }
                    //add to array.
                    $tableList[] = substr($file,0, strlen($file)-4);
                }
            }
            sort($tableList);

            $output .= "<!-- using database : ".$this->dbname.' -->'."\n";

            foreach ($tableList as $tablename) {
                $output .= '<!-- '.$tablename.' : ';
                // check table exists
                //desc  Family;
                try{
                    if ($this->doesTableExist($tablename))
                    {
                        $output .= 'exists -->'."\n";
                    } else {
                        $output .= 'does not exist... -->'."\n";
                        $bSqlSetupOk = false;
                        if ($bMakeChanges){
                            if ($this->createTable($tablename,$bMakeChanges)){
                                $output .= ' Added <b>'.$tablename.'</b> table <br>'."\n";;
                            }else {
                                $output .= ' Failed to created '.$tablename.' table !<br>'."\n";;

                            }
                        }
                    }
                    if ($bCheckColumns){
                        $bSqlSetupOk = $bSqlSetupOk && $this->checkTableStructure($tablename,$bMakeChanges);
                    }
                }
                catch (Exception $e){
                    $output .= "ERROR : ".$e->getMessage().' -->'."\n";
                }
            }

            closedir($opened_file);
            if ($bOutputInfo) echo $output;
            return $bSqlSetupOk;
        }


        function doesTableRowExist($sql){
            $result = mysql_query($sql,$this->dblink);
            try{
                $rowcount = mysql_num_rows($result);
            } catch (Exception $e)
            {
                //echo "[doesTableRowExist] EXCEPTION : $sql<br>";
                $rowcount = 0;
            }
            return $rowcount ;

        }

        function checkRefData($bMakeChanges = false, $bOutputInfo = false)
        {
            $dir = "_setup/_refData";
            $bSqlSetupOk = true;
            $count =0;
            $opened_file = opendir($dir);
            $output = "";
            while ( $file = readdir($opened_file) ) {
                if ( $file != "." && $file != ".." && substr($file, strlen($file)-4, 4) == ".sql") {
                    if (strpos(" ".$file, "phpMyEdit")>0){
                        continue;
                    }
                    //add to array.
                    $tableList[] = substr($file,0, strlen($file)-4);
                }
            }
            sort($tableList);

            $output .= "<!-- using database : ".$this->dbname.' -->'."\n";

            foreach ($tableList as $tablename) {
                $output .= '<!-- '.$tablename.' : ';
                //echo "checking : $tablename<br>";
                // check table exists
                //desc  Family;
                try{
                    if ($this->doesTableExist($tablename))
                    {
                        $output .= "  $tablename does exist<br>";
                        $breslt = $this->checkRefDataTableStructure($tablename,$bMakeChanges);
                        $bSqlSetupOk = $bSqlSetupOk && $breslt;
                    } else {
                        $output .= "  $tablename does NOT exist<br>";
                        $bSqlSetupOk = false;
                    }
                }
                catch (Exception $e){
                    $output .= "ERROR : ".$e->getMessage().' '."\n";
                }
                $output .= " -->\n";
            }

            closedir($opened_file);
            if ($bOutputInfo) echo $output;
            return $bSqlSetupOk;
        }

        function checkRefDataTableStructure($tablename,$bMakeChanges = false){

            $bTableContentsOk = true;
            $sql = $this->getSqlFromFile("_setup/_sql/",$tablename);
            $tablename_tmp = "_tmp";
            //drop tmp table
            try{

                $result = mysql_query("DROP TABLE IF EXISTS `_tmp`;",$this->dblink);
                if (!$result){
                    //throw new Exception("Could not drop table _tmp. Make sure database user has sufficent privileges to create/drop tables");
                }

                $search = "CREATE TABLE IF NOT EXISTS `".$tablename."` (";
                $replace = "CREATE TABLE IF NOT EXISTS `".$tablename_tmp."` (";

                $sql_r = str_replace($search, $replace, $sql);
                //echo $sql_r."<br>";
                // create $sql_r table (as temp)
                $result = mysql_query($sql_r,$this->dblink);

                if (!$result){
                    throw new Exception("Could not create table ".$tablename_tmp.". Make sure database user has sufficent privileges to create/drop tables");
                }

                //now add refdata to tmp table
                $sql_refdata = $this->getSqlFromFile("_setup/_refData/",$tablename);
                $search = "INSERT INTO `".$tablename."` (";
                $replace = "INSERT INTO `".$tablename_tmp."` (";
                $sql_refdata_r = str_replace($search, $replace, $sql_refdata);
                $result = mysql_query($sql_refdata_r,$this->dblink);

                //compare columns against real table
                $bTableContentsOk = $this->compareTableContents($tablename_tmp,$tablename,$bMakeChanges);
                // create columns where required

                //drop tmp table
                $sql_r = "DROP TABLE IF EXISTS `_tmp`;";
                //echo $sql_r."<br>";
                $result = mysql_query($sql_r,$this->dblink);
                if (!$result){
                    throw new Exception("Could not drop table _tmp. Make sure database user has sufficent privileges to create/drop tables");
                }


/* 1. read sql.
   2. replace tablename with _tmp
 * 3. create _tmp
 * 4. check columns against real table
 * 5. create/alter missing columns (do not delete)
 * 6. delete _tmp
*/
            } catch (Exception $e){
                //$this->error500("Error while checking table columns ".$e->getMessage());
            }
            //if ($bTableContentsOk) echo "returning : true<br>";
            //if (!$bTableContentsOk) echo "returning : false<br>";
            return $bTableContentsOk;
        }


        function compareTableContents($sourcetable,$targettable,$bMakeChanges = false){
            // for each column in source check it is in target
            $bTableContentsOk = true;

            //echo "desc ".$sourcetable."<br>";
            $result_source = mysql_query("desc ".$sourcetable,$this->dblink);
            $columns =  array();
            while ($row_source = mysql_fetch_array($result_source)) {
                $columns[] = $row_source['Field'];
            }
            $fieldlist = $this->getCommaList($columns);
            //echo "fieldlist =  ".$fieldlist."<br>";
            $sql_source = "Select ".$fieldlist." from ".$sourcetable;

            //echo "sql_source =".$sql_source."<br>";
            //$result_source = mysql_query("desc ".$sourcetable,$this->dblink);
            //
            //for each row, ensure that other table contains them
            $result_rows_source = mysql_query($sql_source,$this->dblink);
            while ($row_source_data = mysql_fetch_array($result_rows_source)) {

/*
INSERT INTO `Category` (`Category`) VALUES
('Delivery Area'),
('LVR'),
('Post Office Boxes');
*/
                //$row_source_data['Field'];
                $sql_row_target = "Select * from ".$targettable." WHERE ";
                $sql_insert = "INSERT INTO `".$targettable."` ";
                $sql_insert .= " (".$fieldlist.") VALUES (";
                foreach ($columns as $col){
                    $sql_row_target .= $col."='".addslashes($row_source_data[$col])."' AND ";
                    $sql_insert .= " '".addslashes($row_source_data[$col])."',";
                }
                //do select from real table WHERE x=x etc
                //remove last comma
                $sql_insert = substr($sql_insert, 0, strlen($sql_insert)-1).")";
                $sql_row_target .= " TRUE";
                //echo "<br>sql_row_target : ".$sql_row_target."<Br>";
                //echo "<br>sql_insert : ".$sql_insert."<Br>";
                if ($this->doesTableRowExist($sql_row_target)>0){
                    //echo "row exists<br>";
                } else {
                    $bTableContentsOk = false;
                    //echo "row does NOT exist  ($sql_row_target)<br>";
                    if ($bMakeChanges)
                    {
                        //echo "inserting....<br>";
                        mysql_query($sql_insert,$this->dblink);
                        $bTableContentsOk = true;

                    }

                }
            }
            //if ($bTableContentsOk) echo "returning : true<Br>";
            //if (!$bTableContentsOk) echo "returning : false<Br>";
            return $bTableContentsOk;
        }//end function

        function getCommaList($array){
            $str = "";
            foreach( $array as $item){
                if (strlen($str)>0){
                    $str .= ",";
                }
                $str .= $item;
            }
            return $str;
        }

        function checkUserAccounts($bMakeChanges = false)
        {
            $bUserAccountIssue = false;
            if (!$this->doesUserGroupsExist("Administrator"))
            {
                $bUserAccountIssue = true;
                if ($bMakeChanges) $this->insertUserGroups("Administrator", "Site administrator - full access");
                if ($bMakeChanges) $bUserAccountIssue = false;
            }
            if (!$this->doesUserGroupsExist("Editor"))
            {
                $bUserAccountIssue = true;
                if ($bMakeChanges) $this->insertUserGroups("Editor", "Add, edit and remove access only. No user/ref data admin.");
                if ($bMakeChanges) $bUserAccountIssue = false;
            }
            if (!$this->doesUserGroupsExist("Readonly"))
            {
                $bUserAccountIssue = true;
                if ($bMakeChanges) $this->insertUserGroups("Readonly", "No add/remove/edit access to any data. Can access View/reports only");
                if ($bMakeChanges) $bUserAccountIssue = false;
            }
            if (!$this->doesUserGroupsExist("Readonly-Restricted"))
            {
                $bUserAccountIssue = true;
                if ($bMakeChanges) $this->insertUserGroups("Readonly-Restricted", "Read only, but cannot view private fields");
                if ($bMakeChanges) $bUserAccountIssue = false;
            }

            //check users table.
            if (!$this->doesUsersExist()){
                $bUserAccountIssue = true;
                if ($bMakeChanges){
                    $this->addNewAdminUser();
                    $bUserAccountIssue = false;
                }
            }

            if (!$this->doesUsersExist(" WHERE ".$this->userstablename.".Group = 'Administrator'")){
                $bUserAccountIssue = true;
                if ($bMakeChanges){
                    $this->addNewAdminUser();
                    $bUserAccountIssue = false;

                }
            }//Check users have groups (ie. Administraor
            //if not add admin user
        }

        function insertUserGroups($groupname,$groupdesc){
            $sql = 'INSERT INTO '.$this->usergrouptablename.' ('.$groupname.','.$groupdesc.') VALUES("","") ;';
            $result = mysql_query($sql,$this->dblink);
        }

        function doesUserGroupsExist($groupname){
            $sql = 'Select * from '.$this->usergrouptablename;
            $result = mysql_query($sql,$this->dblink);
            if (mysql_num_rows($result)==1){
                return true;
            } else {
                return false;
            }
        }

        function doesUsersExist($where = ''){
            $sql = 'Select * from '.$this->userstablename.' '.$where;
            //echo $sql."<br>";
            $result = mysql_query($sql,$this->dblink);
            if (mysql_num_rows($result)>0){
                return true;
            } else {
                return false;
            }
        }



        function addNewAdminUser(){
            $username = "admin";
            $password = md5("Password1");
            $sql = 'INSERT INTO `'.$this->userstablename.'` ';
            $sql .= "(`id`, `username`, `pw`, `LastLoginTMS`, `ViewPrivateIndicator`, `ModifyIndicator`, `AddEditIndicator`, `DeleteIndicator`, `AdminIndicator`, `ModificationTMS`, `ModificationUserId`, `CreationTMS`, `CreationUserId`, `Group`, `LoginCount`, `Enabled`, `EditCount`, `EmailAddress`) ";
            $sql .= "VALUES (99, '".$username."', '".$password."', '0000-00-00 00:00:00', 'Y', 'Y', 'Y', 'Y', 'Y', '0000-00-00 00:00:00', '', '0000-00-00 00:00:00', '', 'Administrator', 0, 'Y', 0, '')";
            $result = mysql_query($sql,$this->dblink);
            //echo $sql."<br>";
            echo "Added new user (username:".$username.",password:".$password.")<br>";
        }

        function addSampleData($bMakeChanges = false, $bOutputInfo = false)
        {
            $dir = "_setup/_sampleData";
            $bSampleDataOk = true;
            $count =0;
            $opened_file = opendir($dir);
            $output = "";
            while ( $file = readdir($opened_file) ) {
                if ( $file != "." && $file != ".." && substr($file, strlen($file)-4, 4) == ".sql") {
                    if (strpos(" ".$file, "phpMyEdit")>0){
                        continue;
                    }
                    //add to array.
                    $tableList[] = substr($file,0, strlen($file)-4);
                }
            }
            sort($tableList);

            $output .= "<!-- using database : ".$this->dbname.' -->'."\n";

            foreach ($tableList as $tablename) {
                $output .= '<!-- '.$tablename.' : ';
                //echo "checking : $tablename<br>";
                // check table exists
                //desc  Family;
                try{
                    if ($this->doesTableExist($tablename))
                    {
                        $output .= "  $tablename does exist<br>";
                        $breslt = $this->checkRefDataTableStructure($tablename,$bMakeChanges);
                        $bSampleDataOk = $bSampleDataOk && $breslt;
                    } else {
                        $output .= "  $tablename does NOT exist<br>";
                        $bSampleDataOk = false;
                    }
                }
                catch (Exception $e){
                    $output .= "ERROR : ".$e->getMessage().' '."\n";
                }
                $output .= " -->\n";
            }

            closedir($opened_file);
            if ($bOutputInfo) echo $output;
            return $bSampleDataOk;
        }


    }
    ?>